package Messages;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

import ocsf.server.ConnectionToClient;
import Server.SQLConnection;
/**
 * This class implements order report from the DB
*   @author Shlomit Yosfan
 * @author Nofar Sutliman
 */
public class MessageOrderReport {
	
	private ConnectionToClient client ;
	private String[] str; 
	private int NumJanuary;
	private int NumFebruary;
	private int NumMarch;
	private int NumApril;
	private int NumMay;
	private int NumJune;
	private int NumJuly;
	private int NumAugust;
	private int NumSeptember;
	private int NumOctober;
	private int NumNovember;
	private int NumDecember;
	


/**
 * Contractor of this class 
 * @param str
 * @param client
 */
	public MessageOrderReport(String[] str, ConnectionToClient client){
		
		this.client= client ;
		this.str= str; 	
	}
	
	/**
	 * This method generate order report from DB
	 * @throws SQLException
	 * @throws IOException
	 */
	public void OrderReport() throws SQLException, IOException{
	
		SQLConnection sc = new SQLConnection();
		
		String  date1 , date2 ,month1,month2; 
		date1 = str[1];
		date2 = str[2];
		date1=date1.substring(0,4);
		date2=date2.substring(0,4);
		
	
		
		java.sql.PreparedStatement January = sc.getConnection().prepareStatement("SELECT COUNT(ReservaionID) FROM db.reservation WHERE (DeliveryDate >='"+date1+"-01-01' AND DeliveryDate <='"+date1+"-01-31' OR DeliveryDate >='"+date2+"-01-01' AND DeliveryDate <='"+date2+"-01-31') AND DeliveryDate >= '"+str[1]+"' AND DeliveryDate <= '"+str[2]+"';");
		java.sql.PreparedStatement February = sc.getConnection().prepareStatement("SELECT COUNT(ReservaionID) FROM db.reservation WHERE (DeliveryDate >='"+date1+"-02-01' AND DeliveryDate <='"+date1+"-02-31' OR DeliveryDate >='"+date2+"-02-01' AND DeliveryDate <='"+date2+"-02-31') AND DeliveryDate >= '"+str[1]+"' AND DeliveryDate <= '"+str[2]+"';");
		java.sql.PreparedStatement March = sc.getConnection().prepareStatement("SELECT COUNT(ReservaionID) FROM db.reservation WHERE (DeliveryDate >='"+date1+"-03-01' AND DeliveryDate <='"+date1+"-03-31' OR DeliveryDate >='"+date2+"-03-01' AND DeliveryDate <='"+date2+"-03-31')  AND DeliveryDate >= '"+str[1]+"' AND DeliveryDate <= '"+str[2]+"';");
		java.sql.PreparedStatement April = sc.getConnection().prepareStatement("SELECT COUNT(ReservaionID) FROM db.reservation WHERE (DeliveryDate >='"+date1+"-04-01' AND DeliveryDate <='"+date1+"-04-31' OR DeliveryDate >='"+date2+"-04-01' AND DeliveryDate <='"+date2+"-04-31') AND DeliveryDate >= '"+str[1]+"' AND DeliveryDate <= '"+str[2]+"';");
		java.sql.PreparedStatement May = sc.getConnection().prepareStatement("SELECT COUNT(ReservaionID) FROM db.reservation WHERE (DeliveryDate >='"+date1+"-05-01' AND DeliveryDate <='"+date1+"-05-31' OR DeliveryDate >='"+date2+"-05-01' AND DeliveryDate <='"+date2+"-05-31') AND DeliveryDate >= '"+str[1]+"' AND DeliveryDate <= '"+str[2]+"';");
		java.sql.PreparedStatement June = sc.getConnection().prepareStatement("SELECT COUNT(ReservaionID) FROM db.reservation WHERE (DeliveryDate >='"+date1+"-06-01' AND DeliveryDate <='"+date1+"-06-31' OR DeliveryDate >='"+date2+"-06-01' AND DeliveryDate <='"+date2+"-06-31') AND DeliveryDate >= '"+str[1]+"' AND DeliveryDate <= '"+str[2]+"';");
		java.sql.PreparedStatement July = sc.getConnection().prepareStatement("SELECT COUNT(ReservaionID) FROM db.reservation WHERE (DeliveryDate >='"+date1+"-07-01' AND DeliveryDate <='"+date1+"-07-31' OR DeliveryDate >='"+date2+"-07-01' AND DeliveryDate <='"+date2+"-07-31') AND DeliveryDate >= '"+str[1]+"' AND DeliveryDate <= '"+str[2]+"';");
		java.sql.PreparedStatement August = sc.getConnection().prepareStatement("SELECT COUNT(ReservaionID) FROM db.reservation WHERE (DeliveryDate >='"+date1+"-08-01' AND DeliveryDate <='"+date1+"-08-31' OR DeliveryDate >='"+date2+"-08-01' AND DeliveryDate <='"+date2+"-08-31') AND DeliveryDate >= '"+str[1]+"' AND DeliveryDate <= '"+str[2]+"';");
		java.sql.PreparedStatement September = sc.getConnection().prepareStatement("SELECT COUNT(ReservaionID) FROM db.reservation WHERE (DeliveryDate >='"+date1+"-09-01' AND DeliveryDate <='"+date1+"-09-31' OR DeliveryDate >='"+date2+"-09-01' AND DeliveryDate <='"+date2+"-09-31') AND DeliveryDate >= '"+str[1]+"' AND DeliveryDate <= '"+str[2]+"';");
		java.sql.PreparedStatement October = sc.getConnection().prepareStatement("SELECT COUNT(ReservaionID) FROM db.reservation WHERE (DeliveryDate >='"+date1+"-10-01' AND DeliveryDate <='"+date1+"-10-31' OR DeliveryDate >='"+date2+"-10-01' AND DeliveryDate <='"+date2+"-10-31') AND DeliveryDate >= '"+str[1]+"' AND DeliveryDate <= '"+str[2]+"';");
		java.sql.PreparedStatement November = sc.getConnection().prepareStatement("SELECT COUNT(ReservaionID) FROM db.reservation WHERE (DeliveryDate >='"+date1+"-11-01' AND DeliveryDate <='"+date1+"-11-31' OR DeliveryDate >='"+date2+"-11-01' AND DeliveryDate <='"+date2+"-11-31') AND DeliveryDate >= '"+str[1]+"' AND DeliveryDate <= '"+str[2]+"';");
		java.sql.PreparedStatement December = sc.getConnection().prepareStatement("SELECT COUNT(ReservaionID) FROM db.reservation WHERE (DeliveryDate >='"+date1+"-12-01' AND DeliveryDate <='"+date1+"-12-31' OR DeliveryDate >='"+date2+"-12-01' AND DeliveryDate <='"+date2+"-12-31') AND DeliveryDate >= '"+str[1]+"' AND DeliveryDate <= '"+str[2]+"';");
		
		java.sql.ResultSet rsJanuary =  January.executeQuery();
		rsJanuary.next();
		NumJanuary=rsJanuary.getInt(1);
		
		java.sql.ResultSet rsFebruary =  February.executeQuery();
		rsFebruary.next();
		NumFebruary=rsFebruary.getInt(1);
		
		java.sql.ResultSet rsMarch =  March.executeQuery();
		rsMarch.next();
		NumMarch=rsMarch.getInt(1);
		
		java.sql.ResultSet rsApril =  April.executeQuery();
		rsApril.next();
		NumApril=rsApril.getInt(1);
		
		java.sql.ResultSet rsMay =  May.executeQuery();
		rsMay.next();
		NumMay=rsMay.getInt(1);
		
		java.sql.ResultSet rsJune =  June.executeQuery();
		rsJune.next();
		NumJune=rsJune.getInt(1);
		
		java.sql.ResultSet rsJuly =  July.executeQuery();
		rsJuly.next();
		NumJuly=rsJuly.getInt(1);
		
		java.sql.ResultSet rsAugust =  August.executeQuery();
		rsAugust.next();
		NumAugust=rsAugust.getInt(1);
		
		java.sql.ResultSet rsSeptember =  September.executeQuery();
		rsSeptember.next();
		NumSeptember=rsSeptember.getInt(1);
		
		java.sql.ResultSet rsOctober =  October.executeQuery();
		rsOctober.next();
		NumOctober=rsOctober.getInt(1);
		
		java.sql.ResultSet rsNovember =  November.executeQuery();
		rsNovember.next();
		NumNovember=rsNovember.getInt(1);
		
		java.sql.ResultSet rsDecember =  December.executeQuery();
		rsDecember.next();
		NumDecember=rsDecember.getInt(1);
		
		
		
		
		
		ArrayList<Object> bus=new ArrayList<Object>();
		bus.add("OrderReport");
		bus.add(NumJanuary);
		bus.add(NumFebruary);
		bus.add(NumMarch);
		bus.add(NumApril);
		bus.add(NumMay);
		bus.add(NumJune);
		bus.add(NumJuly);
		bus.add(NumAugust);
		bus.add(NumSeptember);
		bus.add(NumOctober);
		bus.add(NumNovember);
		bus.add(NumDecember);
		
		client.sendToClient(bus);
	
	
	}

}		
				        	
			


